﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_can_access_by_team]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_can_access_by_team];
GO
CREATE FUNCTION [dbo].[fn_can_access_by_team]
(
    @Class_ID   int,    -- the id of the class to validate (UDS_Class.ClassID)
    @UserID     int,    -- the name of the user
    @Act_ID     int     -- the id of the permission (UDS_Proc.Proc_ID)
)
RETURNS BIT
AS
BEGIN

--以下判断是判断用户是否再任何一个能访问此类的父类的组中(能访问父类就能访问子类)

--得到所有父组中拥有此权利的组ID
DECLARE @pAssignedTeam TABLE (TeamID int not null);
INSERT INTO @pAssignedTeam (TeamID)
    SELECT Team_ID
        FROM 
            dbo.UDS_Assign_Rule
        WHERE
            Act_ID = @Act_id
            AND Based_On = 2 /* team */
            AND Team_ID IN (
                SELECT ClassID FROM dbo.fn_get_parent_class(@Class_ID)
            );


--返回用户是否在有权利的组中（组成员都具有此权利，不管是组长还是成员）
IF EXISTS (
    SELECT 1
        FROM dbo.UDS_Staff_In_Team A INNER JOIN @pAssignedTeam B ON A.Team_ID = B.TeamID
        WHERE A.Staff_ID = @UserID
)
BEGIN
    RETURN 1;
END

--得到组角色的权利
IF EXISTS (
    SELECT 1
        FROM
            dbo.UDS_Staff_In_Team A,
            dbo.UDS_Activity B
        WHERE
            A.member_type = B.act_id
            AND B.proc_id = @Act_id
            AND A.Staff_ID = @UserID
            AND A.team_id in (
                SELECT ClassID FROM dbo.fn_get_parent_class(@Class_ID)
            )
)
BEGIN
    RETURN 1;
END

-- Access denied.
RETURN 0;
END
GO
